const { sequelize, Dictionary, Material } = require('../models');
const { Op, QueryTypes } = require('sequelize');

class DictionaryService {
	static injector(ctn) {
		return [
			ctn.make('varal.app')
		]
	}
	constructor(app) {
		this.app = app;
	}

	async get(filter) {
		const { name, code, parent_id, work_id } = filter;
		let { root_id, root_code } = filter;

		if (root_code) root_id = await Dictionary.findAll({
			where: {
				[Op.and]: sequelize.where(sequelize.fn("concat", ',', sequelize.col("code"), ','), {
					[Op.regexp]: `,(${root_code.replace(',', '|')}),`
				})
			}
		}).then((data) => data.map(({ id }) => id).join(','));

		/*
		let where = {};
		if (name) where['name'] = { [Op.like]: `%${name}%` };
		if (code) where['code'] = { [Op.like]: `%${code}%` };

		//Boolean('0')=true, Boolean(0)=false
		if (parent_id) where['parent_id'] = parent_id;

		if (root_id) {
			where[Op.and] = sequelize.where(sequelize.fn('FIND_IN_SET', root_id, sequelize.col('parent_ids')), '>', 0)
		}

		const data = await Dictionary.findAll({
			attributes: ['id', ['id', 'value'], 'name', ['name', 'label'], 'code', 'parent_id', 'parent_ids', ['id', 'key'], [sequelize.literal(root_id || 0), 'root_id']],
			where,
			order: [
				['id', 'asc']
			]
		});
		*/

		let sql = `select
			dict.id,
			dict.id value,
			dict.name,
			dict.name label,
			dict.code,
			dict.parent_id,
			dict.parent_ids,
			dict.id \`key\`,
			cast(replace(substring_index(dict.parent_ids,',',2), '0,', '') as unsigned) root_id,
			dict.image,`;

		if (root_code === 'CAR_CATEGORY' || root_id === 13) {
			sql += `work_ids,
			works,`;
		}
		sql += `root.code root_code
		from app_dictionary dict`

		if (root_code === 'CAR_CATEGORY' || root_id === 13) {
			sql += `
			left join (
				select group_concat(work_id) work_ids,group_concat(work.name) works,category_id
					from app_car_category_work ccw
					join app_dictionary work on ccw.work_id=work.id
				group by category_id
			) car_category_work on dict.id=car_category_work.category_id`;
		}

		sql += `
		left join app_dictionary root on replace(substring_index(dict.parent_ids,',',2), '0,', '')=root.id
		`;

		if (name || code) {
			sql += ` left join (
				select dict2.id parent_id,group_concat(dict1.name) names,group_concat(dict1.code) codes from app_dictionary dict1
				join app_dictionary dict2 on find_in_set(dict2.id, dict1.parent_ids)
				group by dict2.id
			) children on children.parent_id=dict.id`;
		}

		sql += ` where dict.delete_time is null`;

		//parent_id为数字0时会返回false，字符0返回true
		if (!isNaN(parent_id)) sql += ` and dict.parent_id=${parent_id}`;
		if (!isNaN(root_id)) sql += ` and concat(',',dict.parent_ids,',') regexp ',(${root_id.toString().replace(',', '|')}),'`;

		if (name) sql += ` and (dict.name like '%${name}%' or children.names like '%${name}%')`;
		if (code) sql += ` and (dict.code like '%${code}%' or children.codes like '%${code}%')`;

		if (!isNaN(work_id)) sql += ` and exists (select 1 from app_car_category_work ccw where ccw.category_id=dict.id and ccw.work_id='${work_id}')`;

		sql += ` order by dict.id asc`;

		const data = await sequelize.query(sql, { type: QueryTypes.SELECT });
		return data;
	}

	async info({ id, code }) {
		if (id) return Dictionary.findByPk(id);
		else if (code) return Dictionary.findOne({ where: { code } });
	}

	async delete(id) {
		await Dictionary.destroy({
			where: {
				id
			}
		});

		await Dictionary.destroy({
			/*
			where: {
				[Op.and]: sequelize.where(sequelize.fn('FIND_IN_SET', id, sequelize.col('parent_ids')), '>', 0)
			}
			*/
			where: [sequelize.where(sequelize.fn('FIND_IN_SET', id, sequelize.col('parent_ids')), '>', 0)]
		});

		return true;
	}

	async save(params) {
		['create_time', 'update_time', 'delete_time'].forEach((d) => {
			delete params[d];
		});
		const { id = 0, parent_id, name, work_ids } = params;

		if (parseInt(parent_id)) {
			const { parent_ids } = await Dictionary.findByPk(parent_id);
			params.parent_ids = `${parent_ids},${parent_id}`;
		} else {
			params.parent_ids = '0';
		}

		//判断名称是否已存在
		const where = {
			name,
			id: { [Op.not]: id }
		};
		if (parseInt(parent_id)) {
			where[Op.and] = [sequelize.where(sequelize.fn('FIND_IN_SET', params.parent_ids.split(',')[1], sequelize.col('parent_ids')), '>', 0)];
		} else {
			where['parent_id'] = 0;
		}

		const dict = await Dictionary.findOne({
			where
		});

		//允许添加相同name的数据
		//if (dict?.id) throw '数据已存在，请勿重复添加';

		let node;
		if (id) {
			//修改所有下级栏目
			node = await Dictionary.findByPk(id);
			await sequelize.query(`update app_dictionary set parent_ids=replace(concat(',', parent_ids), ',${node.parent_ids}', '${params.parent_ids}') where find_in_set('${id}', parent_ids) and delete_time is null`);

			Dictionary.update(params, {
				where: {
					id
				}
			})
		} else {
			node = await Dictionary.create(params);
		}

		if (work_ids) {
			const works = await Dictionary.findAll({
				where: {
					id: { [Op.in]: JSON.parse(work_ids) }
				}
			});
			await node.setWorks(works);
		}

		return node;
	}
}

exports = module.exports = DictionaryService;
